﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using StackExchange.Profiling;
using StackExchange.Profiling.Data;
using Dapper;
using GridReader = Dapper.SqlMapper.GridReader;

namespace Utils
{
    public class DbException : Exception
    {
        public DbException(string errorMessage)
            : base(errorMessage)
        { }
    }
    

    public class DontMap { };

    /// <summary>
    /// Sql Utilities class, support retrieving multiple different lists of objects from different db tables in one query
    /// </summary>
    public class SqlUtilities
    {
        public static void ResetDefaultConnectionString(string connString)
        {
            _defaultConnectionString = connString;
        }

        #region private use only

        /// <summary>
        /// default connection string, here is HotlistConnectionString
        /// </summary>
        private static string _defaultConnectionString = GlobalUtilities.GetInstance().ConnectionString;
        private static Boolean _defaultInTransaction = false;
        private static CommandType _defaultCommandType = CommandType.StoredProcedure;

        /// <summary>
        /// get profiled db connection
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        private static DbConnection GetOpenConnection(string connectionString)
        {
            var conn = new SqlConnection(connectionString);
            conn.Open();
            if (GlobalUtilities.GetInstance().NeedTiming)
            {
                return new ProfiledDbConnection(conn, MiniProfiler.Current);
            }
            else
            {
                return conn;
            }
        }

        /// <summary>
        /// get only one list, and it could contains a composited object, may have master-detail association
        /// </summary>
        /// <typeparam name="TFirst"></typeparam>
        /// <typeparam name="TSecond"></typeparam>
        /// <typeparam name="TThird"></typeparam>
        /// <typeparam name="TFourth"></typeparam>
        /// <typeparam name="TFifth"></typeparam>
        /// <typeparam name="TReturn"></typeparam>
        /// <param name="mapCount"></param>
        /// <param name="connectionString"></param>
        /// <param name="sql"></param>
        /// <param name="map"></param>
        /// <param name="param"></param>
        /// <param name="splitOn"></param>
        /// <param name="commandType"></param>
        /// <param name="inTransaction"></param>
        /// <returns></returns>
        private static IEnumerable<TReturn> QueryInternal<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(int mapCount, string connectionString, string sql, object map, object param = null, string splitOn = "Id", CommandType? commandType = null, Boolean? inTransaction = null)
        {
            IEnumerable<TReturn> results = null;
            DbConnection conn = null;
            Boolean inTran = _defaultInTransaction;
            if (inTransaction.HasValue)
            {
                inTran = inTransaction.Value;
            }
            CommandType commType = _defaultCommandType;
            if (commandType.HasValue)
            {
                commType = commandType.Value;
            }
            try
            {
                //conn = new SqlConnection(connectionString);
                //conn.Open();
                //get profiled db connection
                conn = GetOpenConnection(connectionString);
                if (inTran)
                {
                    DbTransaction transaction = null;
                    try
                    {
                        transaction = conn.BeginTransaction();

                        switch (mapCount)
                        {
                            //for update, insert, delete, etc (Execute)
                            case 0:
                                object o = conn.Execute(sql, param, transaction, null, commType);
                                TReturn r = (TReturn)o;
                                List<TReturn> l = new List<TReturn>();
                                l.Add(r);
                                results = l.AsEnumerable();
                                break;
                            //for select (Query)
                            case 1:
                                results = conn.Query<TReturn>(sql, param, transaction, true, null, commType);
                                break;
                            case 2:
                                results = conn.Query<TFirst, TSecond, TReturn>(sql, GenerateMapper<TFirst, TSecond, TReturn>(map), param, transaction, true, splitOn, null, commType);
                                break;
                            case 3:
                                results = conn.Query<TFirst, TSecond, TThird, TReturn>(sql, GenerateMapper<TFirst, TSecond, TThird, TReturn>(map), param, transaction, true, splitOn, null, commType);
                                break;
                            case 4:
                                results = conn.Query<TFirst, TSecond, TThird, TFourth, TReturn>(sql, GenerateMapper<TFirst, TSecond, TThird, TFourth, TReturn>(map), param, transaction, true, splitOn, null, commType);
                                break;
                            case 5:
                                results = conn.Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(sql, GenerateMapper<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(map), param, transaction, true, splitOn, null, commType);
                                break;
                            default:
                                break;
                        }

                        transaction.Commit();
                    }
                    catch
                    {
                        //log
                        if (transaction != null)
                        {
                            transaction.Rollback();
                        }
                    }
                    finally
                    {
                        if (transaction != null)
                        {
                            transaction.Dispose();
                            transaction = null;
                        }
                    }
                }
                else
                {
                    //for update, insert, delete, etc (Execute)
                    switch (mapCount)
                    {
                        case 0:
                            object o = conn.Execute(sql, param, null, null, commType);
                            TReturn r = (TReturn)o;
                            List<TReturn> l = new List<TReturn>();
                            l.Add(r);
                            results = l.AsEnumerable();
                            break;
                        //for select (Query)
                        case 1:
                            results = conn.Query<TReturn>(sql, param, null, true, null, commType);
                            break;
                        case 2:
                            results = conn.Query<TFirst, TSecond, TReturn>(sql, GenerateMapper<TFirst, TSecond, TReturn>(map), param, null, true, splitOn, null, commType);
                            break;
                        case 3:
                            results = conn.Query<TFirst, TSecond, TThird, TReturn>(sql, GenerateMapper<TFirst, TSecond, TThird, TReturn>(map), param, null, true, splitOn, null, commType);
                            break;
                        case 4:
                            results = conn.Query<TFirst, TSecond, TThird, TFourth, TReturn>(sql, GenerateMapper<TFirst, TSecond, TThird, TFourth, TReturn>(map), param, null, true, splitOn, null, commType);
                            break;
                        case 5:
                            results = conn.Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(sql, GenerateMapper<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(map), param, null, true, splitOn, null, commType);
                            break;
                        default:
                            break;
                    }
                }
            }
            catch(Exception e)
            {
                throw new DbException(e.Message);
            }
            finally
            {
                if (conn != null)
                {
                    try
                    {
                        conn.Close();
                    }
                    catch { }
                    conn.Dispose();
                    conn = null;
                }
            }
            return results;
        }

        private static IEnumerable<TReturn> QueryInternalDefault<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(int mapCount, string sql, object map, object param = null, string splitOn = "Id", CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryInternal<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(mapCount, _defaultConnectionString, sql, map, param, splitOn, commandType, inTransaction);
        }

        private static Func<TFirst, TSecond, TReturn> GenerateMapper<TFirst, TSecond, TReturn>(object map)
        {
            return (Func<TFirst, TSecond, TReturn>)map;
        }
        private static Func<TFirst, TSecond, TThird, TReturn> GenerateMapper<TFirst, TSecond, TThird, TReturn>(object map)
        {
            return (Func<TFirst, TSecond, TThird, TReturn>)map;
        }
        private static Func<TFirst, TSecond, TThird, TFourth, TReturn> GenerateMapper<TFirst, TSecond, TThird, TFourth, TReturn>(object map)
        {
            return (Func<TFirst, TSecond, TThird, TFourth, TReturn>)map;
        }
        private static Func<TFirst, TSecond, TThird, TFourth, TFifth, TReturn> GenerateMapper<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(object map)
        {
            return (Func<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>)map;
        }

        /// <summary>
        /// get multiple lists, but each list contains a single object, no master-detail association
        /// </summary>
        /// <typeparam name="TFirst"></typeparam>
        /// <typeparam name="TSecond"></typeparam>
        /// <typeparam name="TThird"></typeparam>
        /// <typeparam name="TFourth"></typeparam>
        /// <typeparam name="TFifth"></typeparam>
        /// <param name="connectionString"></param>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="commandType"></param>
        /// <param name="inTransaction"></param>
        /// <returns></returns>
        private static Queue<dynamic> QueryMultipleInternal<TFirst, TSecond, TThird, TFourth, TFifth>(string connectionString, string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            Queue<dynamic> q = new Queue<dynamic>();
            SqlMapper.GridReader result = null;
            DbConnection conn = null;
            Boolean inTran = _defaultInTransaction;
            if (inTransaction.HasValue)
            {
                inTran = inTransaction.Value;
            }
            CommandType commType = _defaultCommandType;
            if (commandType.HasValue)
            {
                commType = commandType.Value;
            }
            try
            {
                //conn = new SqlConnection(connectionString);
                //conn.Open();
                //get profiled db connection
                conn = GetOpenConnection(connectionString);
                if (inTran)
                {
                    DbTransaction transaction = null;
                    try
                    {
                        transaction = conn.BeginTransaction();
                        result = conn.QueryMultiple(sql, param, transaction, null, commType);
                        transaction.Commit();
                    }
                    catch
                    {
                        //log
                        if (result != null)
                        {
                            result.Dispose();
                            result = null;
                        }
                        if (transaction != null)
                        {
                            transaction.Rollback();
                        }
                    }
                    finally
                    {
                        if (transaction != null)
                        {
                            transaction.Dispose();
                            transaction = null;
                        }
                    }
                }
                else
                {
                    result = conn.QueryMultiple(sql, param, null, null, commType);
                }

                if (result != null)
                {

                    if (typeof(TFirst) != typeof(DontMap))
                    {
                        q.Enqueue(result.Read<TFirst>().ToList());
                    }
                    if (typeof(TSecond) != typeof(DontMap))
                    {
                        q.Enqueue(result.Read<TSecond>().ToList());
                    }
                    if (typeof(TThird) != typeof(DontMap))
                    {
                        q.Enqueue(result.Read<TThird>().ToList());
                    }
                    if (typeof(TFourth) != typeof(DontMap))
                    {
                        q.Enqueue(result.Read<TFourth>().ToList());
                    }
                    if (typeof(TFifth) != typeof(DontMap))
                    {
                        q.Enqueue(result.Read<TFifth>().ToList());
                    }
                }

            }
            catch(Exception e)
            {
                //log
                throw new DbException(e.Message);
            }
            finally
            {
                if (result != null)
                {
                    result.Dispose();
                    result = null;
                }
                if (conn != null)
                {
                    try
                    {
                        conn.Close();
                    }
                    catch { }
                    conn.Dispose();
                    conn = null;
                }
            }
            return q;
        }

        private static Queue<dynamic> QueryMultipleInternalDefault<TFirst, TSecond, TThird, TFourth, TFifth>(string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryMultipleInternal<TFirst, TSecond, TThird, TFourth, TFifth>(_defaultConnectionString, sql, param, commandType, inTransaction);
        }
        
        /// <summary>
        /// get a set of grid results 
        /// </summary>
        /// <param name="reader"></param>
        /// <param name="connectionString"></param>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="commandType"></param>
        /// <param name="inTransaction"></param>
        /// <returns></returns>
        private static Queue<dynamic> QueryGridInternal(Func<GridReader, Queue<dynamic>> reader, string connectionString, string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            Queue<dynamic> q = null;
            SqlMapper.GridReader result = null;
            DbConnection conn = null;
            Boolean inTran = _defaultInTransaction;
            if (inTransaction.HasValue)
            {
                inTran = inTransaction.Value;
            }
            CommandType commType = _defaultCommandType;
            if (commandType.HasValue)
            {
                commType = commandType.Value;
            }
            try
            {
                //conn = new SqlConnection(connectionString);
                //conn.Open();
                //get profiled db connection
                conn = GetOpenConnection(connectionString);
                if (inTran)
                {
                    DbTransaction transaction = null;
                    try
                    {
                        transaction = conn.BeginTransaction();
                        result = conn.QueryMultiple(sql, param, transaction, null, commType);
                        q = reader(result);
                        transaction.Commit();
                    }
                    catch
                    {
                        //log
                        if (transaction != null)
                        {
                            transaction.Rollback();
                        }
                    }
                    finally
                    {
                        if (transaction != null)
                        {
                            transaction.Dispose();
                            transaction = null;
                        }
                    }
                }
                else
                {
                    result = conn.QueryMultiple(sql, param, null, null, commType);
                    q = reader(result);
                }
            }
            catch(Exception e)
            {
                //log
                //for debug
                throw new DbException(e.Message);
            }
            finally
            {
                if (conn != null)
                {
                    try
                    {
                        conn.Close();
                    }
                    catch { }
                    conn.Dispose();
                    conn = null;
                }
            }
            return q;
        }

        private static Queue<dynamic> QueryGridInternalDefault(Func<GridReader, Queue<dynamic>> reader, string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryGridInternal(reader, _defaultConnectionString, sql, param, commandType, inTransaction);
        }

        #endregion 


        #region public functions for returning only one list, but can map to multiple objects for one row

        public static int Execute(string connectionString, string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryInternal<DontMap, DontMap, DontMap, DontMap, DontMap, int>(0, connectionString, sql, null, param, null, commandType, inTransaction).Single<int>();
        }

        public static IEnumerable<T> Query<T>(string connectionString, string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryInternal<DontMap, DontMap, DontMap, DontMap, DontMap, T>(1, connectionString, sql, null, param, null, commandType, inTransaction);
        }

        public static IEnumerable<TReturn> Query<TFirst, TSecond, TReturn>(string connectionString, string sql, Func<TFirst, TSecond, TReturn> map, object param = null, string splitOn = "Id", CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryInternal<TFirst, TSecond, DontMap, DontMap, DontMap, TReturn>(2, connectionString, sql, map, param, splitOn, commandType, inTransaction);
        }

        public static IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TReturn>(string connectionString, string sql, Func<TFirst, TSecond, TThird, TReturn> map, object param = null, string splitOn = "Id", CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryInternal<TFirst, TSecond, TThird, DontMap, DontMap, TReturn>(3, connectionString, sql, map, param, splitOn, commandType, inTransaction);
        }

        public static IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TReturn>(string connectionString, string sql, Func<TFirst, TSecond, TThird, TFourth, TReturn> map, object param = null, string splitOn = "Id", CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryInternal<TFirst, TSecond, TThird, TFourth, DontMap, TReturn>(4, connectionString, sql, map, param, splitOn, commandType, inTransaction);
        }

        public static IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(string connectionString, string sql, Func<TFirst, TSecond, TThird, TFourth, TFifth, TReturn> map, object param = null, string splitOn = "Id", CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryInternal<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(5, connectionString, sql, map, param, splitOn, commandType, inTransaction);
        }

        //default query (hotlist connection)
        public static int Execute(string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryInternalDefault<DontMap, DontMap, DontMap, DontMap, DontMap, int>(0, sql, null, param, null, commandType, inTransaction).Single<int>();
        }

        public static IEnumerable<T> Query<T>(string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryInternalDefault<DontMap, DontMap, DontMap, DontMap, DontMap, T>(1, sql, null, param, null, commandType, inTransaction);
        }

        public static IEnumerable<TReturn> Query<TFirst, TSecond, TReturn>(string sql, Func<TFirst, TSecond, TReturn> map, object param = null, string splitOn = "Id", CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryInternalDefault<TFirst, TSecond, DontMap, DontMap, DontMap, TReturn>(2, sql, map, param, splitOn, commandType, inTransaction);
        }

        public static IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TReturn>(string sql, Func<TFirst, TSecond, TThird, TReturn> map, object param = null, string splitOn = "Id", CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryInternalDefault<TFirst, TSecond, TThird, DontMap, DontMap, TReturn>(3, sql, map, param, splitOn, commandType, inTransaction);
        }

        public static IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TReturn> map, object param = null, string splitOn = "Id", CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryInternalDefault<TFirst, TSecond, TThird, TFourth, DontMap, TReturn>(4, sql, map, param, splitOn, commandType, inTransaction);
        }

        public static IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(string sql, Func<TFirst, TSecond, TThird, TFourth, TFifth, TReturn> map, object param = null, string splitOn = "Id", CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryInternalDefault<TFirst, TSecond, TThird, TFourth, TFifth, TReturn>(5, sql, map, param, splitOn, commandType, inTransaction);
        }

        #endregion

        #region public functions for returning multiple lists, but map to only one object for one row

        //return at most five queries now, we can extend it easily
        public static Queue<dynamic> QueryMultiple<TFirst, TSecond, TThird, TFourth, TFifth>(string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryMultipleInternalDefault<TFirst, TSecond, TThird, TFourth, TFifth>(sql, param, commandType, inTransaction);
        }

        public static Queue<dynamic> QueryMultiple<TFirst, TSecond, TThird, TFourth>(string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryMultipleInternalDefault<TFirst, TSecond, TThird, TFourth, DontMap>(sql, param, commandType, inTransaction);
        }

        public static Queue<dynamic> QueryMultiple<TFirst, TSecond, TThird>(string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryMultipleInternalDefault<TFirst, TSecond, TThird, DontMap, DontMap>(sql, param, commandType, inTransaction);
        }

        public static Queue<dynamic> QueryMultiple<TFirst, TSecond>(string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryMultipleInternalDefault<TFirst, TSecond, DontMap, DontMap, DontMap>(sql, param, commandType, inTransaction);
        }

        public static Queue<dynamic> QueryMultiple<TFirst, TSecond, TThird, TFourth, TFifth>(string connectionString, string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryMultipleInternal<TFirst, TSecond, TThird, TFourth, TFifth>(connectionString, sql, param, commandType, inTransaction);
        }

        public static Queue<dynamic> QueryMultiple<TFirst, TSecond, TThird, TFourth>(string connectionString, string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryMultipleInternal<TFirst, TSecond, TThird, TFourth, DontMap>(connectionString, sql, param, commandType, inTransaction);
        }

        public static Queue<dynamic> QueryMultiple<TFirst, TSecond, TThird>(string connectionString, string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryMultipleInternal<TFirst, TSecond, TThird, DontMap, DontMap>(connectionString, sql, param, commandType, inTransaction);
        }

        public static Queue<dynamic> QueryMultiple<TFirst, TSecond>(string connectionString, string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryMultipleInternal<TFirst, TSecond, DontMap, DontMap, DontMap>(connectionString, sql, param, commandType, inTransaction);
        }
        #endregion

        #region public functions for returning multiple lists, and map to multiple objects for one row
        public static Queue<dynamic> QueryGrid(Func<GridReader, Queue<dynamic>> reader, string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryGridInternalDefault(reader, sql, param, commandType, inTransaction);
        }

        public static Queue<dynamic> QueryGrid(Func<GridReader, Queue<dynamic>> reader, string connectionString, string sql, object param = null, CommandType? commandType = null, Boolean? inTransaction = null)
        {
            return QueryGridInternal(reader, connectionString, sql, param, commandType, inTransaction);
        }
        #endregion


    }
}